package org.gosdt.ew.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;


/**
 * Describe data base object Vocabulary, usual it's one word 
 * @author anton
 *
 */
public class Vocabulary extends DataBaseAccess {
	
	/**
	 * selector for vocabulary, need for query
	 * @author anton
	 *
	 */
	public class VocabularySelection{
		/** language name */
		private String language = "";
		/** 
		 * Sign for {@link wordValue} such as "like", "starting with", "=", "<>" ...
		 */
		private String sqlWordOperator = "";
		/** value for compare with word value */
		private String wordValue = "";
		/** Sign for {@link interpretationValue} such as "like", "=", "<>" ...*/
		private String sqlInterpretationOperator = "";
		/** value for compare with interpretation value*/
		private String interpretationValue = "";
		/** one word id in table WordInTopic*/
		String wordId = "";
		/** select word from Vocabulary by existing in WordInTopic table*/
		public String topicName = "";
		
		/**
		 * get language name for selecting
		 * @return - String - language name
		 */
		public String getLanguage(){
			return language;
		}
		
		/**
		 * get sign value for compare word
		 * @return - String - sign
		 */
		public String getSqlWordOperator(){
			return sqlWordOperator;
		}
		/**
		 * get word value
		 * @return - String - word name
		 */
		public String getWordValue(){
			return wordValue;
		}
		/**
		 * get sign value for compare interpretation
		 * @return - String - sign
		 */
		public String getSqlInterpretationOperator(){
			return sqlInterpretationOperator;
		}
		/**
		 * get interpretation value
		 * @return - String - interpretation
		 */
		public String getInterpretationValue(){
			return interpretationValue;
		}
		/**
		 * get word for join WordInTopic
		 * @return - String - word name
		 */
		public String getWordId(){
			return wordId;
		}
		/**
		 * get topic name for WordInTopic
		 * @return - String - topic name
		 */
		public String getTopicName(){
			return topicName;
		}

		public void setLanguage(String language) {
			this.language = language;
		}

		public void setInterpretationValue(String interpretationValue) {
			this.interpretationValue = interpretationValue;
		}

		public void setSqlInterpretationOperator(
				String sqlInterpretationOperator) {
			this.sqlInterpretationOperator = sqlInterpretationOperator;
		}

		public void setWordValue(String wordValue) {
			this.wordValue = wordValue;
		}

		public void setSqlWordOperator(String sqlWordOperator) {
			this.sqlWordOperator = sqlWordOperator;
		}
		
	}

	static public String TABLE_NAME = "Vocabulary";
	/** word id in table*/
	private int id = 0;
	/** word value*/
	private String word = "";
	/** interpretation value*/
	private String interpretation = "";
	/** alphabet value*/
	private Alphabet curComb;
	/** array for all topics for one word*/
	private ArrayList<Topic> curTopics;
	/** selector for vocabulary */
	public VocabularySelection mySelect = new VocabularySelection();
	/** 
	 * constructor for Vocabulary
	 * @param letter - alphabet object for current letter
	 */
	public Vocabulary(Alphabet letter) {
		super(TABLE_NAME);
		curComb = letter;
	}
	/**
	 * constructor for vocabulary
	 * @param wordId - id value form db
	 */
	public Vocabulary(int wordId) {
		super(TABLE_NAME);
		fillSellfByID(wordId);
	}
	/**
	 * select topics list for current word
	 * @return - topic list
	 */
	public ArrayList<Topic> getWordTopicsList(){
		curTopics = Topic.getTopicListByWord("=", Integer.toString(id));
		return curTopics; 
	}
	/**
	 * add topic to topic list for current word
	 * @param topic - topic for adding to word 
	 */
	public void setTopicToWord(Topic topic){
		if (curTopics == null)
			getWordTopicsList();
		if (curTopics == null)
			curTopics = new ArrayList<Topic>();
		if (curTopics.indexOf(topic) == -1)
			curTopics.add(topic);
	}
	/**
	 * get word id in DB
	 * @return - word id
	 */
	public int getId(){
		return id;
	}
	/**
	 * get word value for current word
	 * @return - word
	 */
	public String getWord(){
		return word;
	}
	/**
	 * get interpretation for current word
	 * @return - interpretation
	 */
	public String getInterpretation(){
		return interpretation;
	}
	/**
	 * get alphabet latter for current word
	 * @return
	 */
	public Alphabet getCurrentLetter(){
		return curComb;
	}
	
	/**
	 * set word value, if word exists in DB then while fill and {@link curTopics}
	 * @param newWord - new word value
	 */
	public void setWord(String newWord){
		if (!newWord.equals(word)) {
			word = newWord;
			
			this.mySelect.setSqlWordOperator("=");
			this.mySelect.setWordValue(newWord.toLowerCase());
			this.mySelect.setLanguage(curComb.getLanguage());
			
			ArrayList<Vocabulary> voc = getList(this.mySelect);
			if (voc.size() > 0){
				this.id = voc.get(0).id;
				this.interpretation = voc.get(0).interpretation;
				this.curTopics = voc.get(0).getWordTopicsList();
			}
		}
	}
	
	/** 
	 * set new interpretation for current word
	 * @param newInterpretation - interpretation
	 */
	public void setInterpretation(String newInterpretation){
		interpretation = newInterpretation;
	}
	/**
	 * Select word list from DB which is not studied yet.
	 * @param wordsRequired - count how many words need to be returned
	 * @param profileName - profile which do study
	 * @param topicName - if it's not "" then select by special topic 
	 * @return - ArrayList of EW_Vocabualry
	 */
	public ArrayList<Vocabulary> getListOfNewWords(int wordsRequired, String profileName, String topicName){
		String selectSQL = "SELECT * FROM " + TABLE_NAME +" v";
		if (topicName.equals("")){
			selectSQL += " WHERE (NOT EXISTS (SELECT * FROM " + StudiedWords.TABLE_NAME + 
						   " sw WHERE sw.wordId = v.id and sw.profile = '" + profileName + "')) and v.language = '" + curComb.getLanguage() + "'";
		}
		else {
			selectSQL += " JOIN WordInTopic wit on v.Id = wit.wordId WHERE " +
			   " (NOT EXISTS (SELECT * FROM " + StudiedWords.TABLE_NAME + 
			   " sw WHERE sw.wordId = v.id and sw.profile = '" + profileName + "')) and v.language = '" + curComb.getLanguage() + 
			   "' and wit.topicName = '" + topicName + "'";
		}
		ArrayList<Vocabulary> list = new ArrayList<Vocabulary>();
		try {
			ResultSet rs = super.db.getConn().prepareStatement(selectSQL).executeQuery();
			while (rs.next()) {
				Vocabulary item = new Vocabulary(new Alphabet(rs.getString("language"), rs.getString("alphabetLatter")));
				item.id = rs.getInt("id");
				item.interpretation = rs.getString("interpretation");
				item.word = rs.getString("word");
				list.add(item);
				if (list.size() == wordsRequired) {
					rs.close();
					return list;
				}
			}
			rs.close();
			return list;
		} 
		catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * fill all values by given id
	 * @param id - word id for select
	 */
	private void fillSellfByID(int id){
		HashMap<String, String> select = new HashMap<String, String>();
		select.put("id", ((Integer)id).toString());
		try {
			ResultSet rs = getListByDetail(select);
			while (rs.next()) {
				curComb = new Alphabet(rs.getString("language"), rs.getString("alphabetLatter"));
				this.id = rs.getInt("id");
				this.interpretation = rs.getString("interpretation");
				this.word = rs.getString("word");
				break;
			}
			rs.close();
		} 
		catch (Exception except) {
			except.printStackTrace();
		}
		
	}

	@Override
	protected ResultSet getListByDetail(HashMap<?, ?> keys) {
		String selectSQL = "SELECT * From " + TABLE_NAME + " where";
		int keysCount = keys.size();
		if (keysCount == 0)	{
			selectSQL += " 1 = 1";
		}
		else {
			if (keys.get("id") != null){
				selectSQL += " id = " +	keys.get("id");
				if (keysCount > 1){
					selectSQL += " and ";
					keysCount--;
				}
			};
			if ((keys.get("language") != null) & (keys.get("alphabetLatter") != null)){
				selectSQL += " language = '" + keys.get("language") + "' and alphabetLatter = '" + keys.get("alphabetLatter") + "'";
				if (keysCount > 2) {
					selectSQL += " and ";
					keysCount -= 2;
				}
			};
			if (keys.get("interpretation") != null) 
				selectSQL += " interpretation like '%" + keys.get("interpretation") + "%'";
		};
		
		try {
			return super.executeAnyQuery(selectSQL);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * return list of all item's which satisfy the request of keys
	 * @param keys	array of request parameters key can be:
	 * 	1 - language get string, name of language
	 *  2 - letter get string, letter sign 
	 *  3 - id get string, unique number of word
	 *  4 - interpretation get string, all or part of interpretation
	 * @return	list of EW_Vocabulary.
	 */
	public ArrayList<Vocabulary> getList(VocabularySelection select){
		String selectSQL = "SELECT v.* From " + TABLE_NAME;
		if (!select.topicName.equals("")){
			selectSQL += " v JOIN WordInTopic wit ON wit.wordId = v.id";
		}
		else {
			selectSQL += " v ";	
		}
		selectSQL += " where 1=1 ";
		selectSQL += "and v.language = '" + select.getLanguage() + "' ";
		if (select.getSqlWordOperator().length() > 0){
			selectSQL += "and v.word " + select.getSqlWordOperator() + " '" + select.getWordValue() + "' "; 
			//selectSQL += "and alphabetLatter = '" + select.getWordValue().substring(0, 1) + "' ";
		}
		if (select.getSqlInterpretationOperator().length() > 0){
			selectSQL += "and v.interpretation " + select.getSqlInterpretationOperator() + " '" + select.getInterpretationValue() + "' ";
		}
		
		if (select.getWordId().length() > 0){
			selectSQL += "and v.wordId = " + select.getWordId();
		}
		
		if (!select.topicName.equals("")){
			selectSQL += " and wit.topicName = '" + select.topicName + "'";
		}
		ArrayList<Vocabulary> list = new ArrayList<Vocabulary>();
		
		try {
			ResultSet rs = super.executeAnyQuery(selectSQL);
			while (rs.next()) {
				Vocabulary item = new Vocabulary(new Alphabet(rs.getString("language"), rs.getString("alphabetLatter")));
				item.id = rs.getInt("id");
				item.interpretation = rs.getString("interpretation");
				item.word = rs.getString("word");
				list.add(item);
			}
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * delete one word from DB
	 * @return - integer - deleting result
	 */
	public int deleteCurrentWord(){
		ArrayList<String> keys = new ArrayList<String>();
		keys.add("id = " + ((Integer)id).toString());
		return super.deleteOneRecord(keys);
	}
	/**
	 * get new id for just inserted words
	 * @return - new id
	 */
	private int getWordIdFromDB(){
		String sql = "select max(id) from vocabulary";
		int id = -1;
		try {
			ResultSet rs = super.executeAnyQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				rs.close();
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}		
		return id;
	}
	
	/**
	 * insert current record in DB. if id != 0 then update current record
	 * @return - sql result
	 */
	public int insertCurrentRecord(){
		if (id != 0) 
			return updateCurrentRecord();
		try {
			PreparedStatement exec = super.db.getConn().prepareStatement("INSERT INTO Vocabulary (WORD, INTERPRETATION, ALPHABETLATTER, LANGUAGE) values (?,?,?,?)");
			exec.setString(1, word.toLowerCase().replace("''", "'"));
			exec.setString(2, interpretation.toLowerCase());
			exec.setString(3, curComb.getLetter());
			exec.setString(4, curComb.getLanguage());
			exec.execute();
			this.id = getWordIdFromDB();
			joinCurWordAndTopics();
			return exec.getResultSetType();
		} 
		catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	/**
	 * update current record in DB
	 * @return - sql result
	 */
	public int updateCurrentRecord(){
		ArrayList<String> where = new ArrayList<String>();
		where.add("id = " + ((Integer)id).toString());
		
		ArrayList<String> sets = new ArrayList<String>();
		sets.add("word = '" + word.replace("'", "''") + "'");
		sets.add("interpretation = '" + interpretation.replace("'", "''") + "'"); 
		sets.add("alphabetLatter = '" + curComb.getLetter() + "'");
		sets.add("language = '" + curComb.getLanguage() + "'");
		joinCurWordAndTopics();
		return super.updateOneRecord(where, sets);
	}
	
	/**
	 * create new links for word and topics
	 */
	private void joinCurWordAndTopics(){
		if ((curTopics == null) || (curTopics.size() == 0))
			return;
		
		ArrayList<Topic> inDB = Topic.getTopicListByWord("=", Integer.toString(this.id));
		for (Topic ewTopic : curTopics) {
			if (inDB.contains(ewTopic))
				continue;
			else
				ewTopic.joinWordAndTopic(this);
		}
	}

}
